﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.IO;
using System.Net;
using System.Text.RegularExpressions;

namespace DTS
{
    class DbAdapter
    {
        private string connectionString = "Server=127.0.0.1;Uid=root;Pwd=;Database=dts;";
        
        // SPOLKI
        public void InsertSpolki(List<Spolka> listaSpolek) //Tworzenie zapytania dodającego nowe spółki do bazy danych
        {
            foreach (Spolka s in listaSpolek)
            {
                InsertSpolka(s);
            }
        }
        public void InsertSpolka(Spolka s) //Tworzenie zapytania dodającego nową spółkę do bazy danych
        {
            string commandText = "INSERT INTO `dts`.`spolka` (`symbol`, `nazwa`, `liczbaAkcji`, `freefloat`) VALUES('";
            commandText += s.GetSymbol() + "', '";
            commandText += s.GetPelnaNazwa() + "', ";
            commandText += s.GetLiczbaAkcji().ToString() +", ";
            commandText += s.GetFreeFloat().ToString() + ")";

            ExecuteNonQuery(commandText);

        }
        public List<Spolka> SelectSpolki()//Pobieranie z bazy danych listy spółek
        {
            List<Spolka> listaSpolek = new List<Spolka>();
            
            string selectCommand = "SELECT symbol, nazwa, liczbaAkcji, kapitalizacja, sektor, freefloat FROM `dts`.`spolka`;";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable spolki = new System.Data.DataTable();
            da.Fill(spolki);

            for (int row = 0; row < spolki.Rows.Count; row++)
            {
                Spolka s = new Spolka(spolki.Rows[row].ItemArray[0].ToString(), spolki.Rows[row].ItemArray[1].ToString(), long.Parse(spolki.Rows[row].ItemArray[2].ToString()), Double.Parse(spolki.Rows[row].ItemArray[3].ToString()), spolki.Rows[row].ItemArray[4].ToString(), Double.Parse(spolki.Rows[row].ItemArray[5].ToString()));
                listaSpolek.Add(s);
            }
            return listaSpolek;
        }
        public Spolka SelectSpolka(string sym)//Pobieranie z bazy danych informacji o spółce
        {
            Spolka s = new Spolka();
            string selectCommand = "SELECT symbol, nazwa, liczbaAkcji, kapitalizacja, sektor, freefloat FROM `dts`.`spolka` WHERE symbol = '"+sym +"';";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable spolki = new System.Data.DataTable();
            da.Fill(spolki);

            for (int row = 0; row < spolki.Rows.Count; row++)
            {
                s.SetSymbol(spolki.Rows[row].ItemArray[0].ToString());
                s.SetPelnaNazwa(spolki.Rows[row].ItemArray[1].ToString());
                s.SetLiczbaAkcji(long.Parse(spolki.Rows[row].ItemArray[2].ToString()));
                s.SetKapitalizacja(Double.Parse(spolki.Rows[row].ItemArray[3].ToString()));
                s.SetSektor(spolki.Rows[row].ItemArray[4].ToString());
                s.SetFreeFloat(Double.Parse(spolki.Rows[row].ItemArray[5].ToString()));
            }
            return s;
        }
        public void UpdateSektorSpolki(string symbol, string sektor)
        {
            string command = "UPDATE `dts`.`spolka` SET `sektor` = '" + sektor + "' WHERE symbol = '" + symbol + "';";
            ExecuteNonQuery(command);
        }
        public List<string> PobierzListeSpolek()
        {
            string s = DownloadWebPage("http://stooq.pl/notowania/?kat=g2");
            //string regexPattern = "=[0-9a-z]{3}>[a-zA-Z0-9]*</a>";
            string regexPattern = "q\\/\\?s=[0-9a-z]{3}";
            Regex.Replace(s, "'(href=)'", " ");
            Regex.Replace(s, "%d", " ");
            Regex re = new Regex(regexPattern);

            List<string> result = new List<string>();
            Regex r = new Regex(regexPattern);
            string tmp;
            string z;
            for (Match m = r.Match(s); m.Success; m = m.NextMatch())
            {
                tmp = m.ToString();
                z = tmp.Substring(5, 3);
                result.Add(z);
            }

            return result;
        }
        public List<Spolka> PobierzDaneSpolek(List<string> listaSpolek)
        {
            List<Spolka> listaDanychSpolek = new List<Spolka>();

            foreach (string spolka in listaSpolek)
            {
                string html = DownloadWebPage("http://stooq.pl/q/h/?s=" + spolka);
                Spolka sp = new Spolka(spolka, "", 0, 0.0, "", 0.0);

                string fFRegexPattern = "Free Float: [0-9]*";
                string aRegexPattern = "Akcjonariat: [A-Ża-ż0-9 ]*";
                string lARegexPattern = "Liczba wszystkich akcji: [0-9,]*";
                string tmp;

                List<string> result = new List<string>();
                Regex r = new Regex(fFRegexPattern);

                for (Match m = r.Match(html); m.Success; m = m.NextMatch())
                {
                    tmp = m.ToString();
                    tmp = tmp.Substring(12, tmp.Length - 12);
                    try
                    {
                        sp.SetFreeFloat(double.Parse(tmp));
                    }
                    catch
                    {
                        sp.SetFreeFloat(-1);
                    }
                }
                result.Clear();
                r = new Regex(aRegexPattern);

                for (Match m = r.Match(html); m.Success; m = m.NextMatch())
                {
                    tmp = m.ToString();
                    tmp = tmp.Substring(13, tmp.Length - 14);
                    sp.SetPelnaNazwa(tmp);
                }
                result.Clear();
                r = new Regex(lARegexPattern);

                for (Match m = r.Match(html); m.Success; m = m.NextMatch())
                {
                    tmp = m.ToString();
                    tmp = tmp.Substring(24, tmp.Length - 24);
                    string[] liczbaAkcji = tmp.Split(',');
                    tmp = "";
                    foreach (string czlon in liczbaAkcji)
                        tmp += czlon;
                    sp.SetLiczbaAkcji(long.Parse(tmp));
                }
                listaDanychSpolek.Add(sp);
            }

            return listaDanychSpolek;
        }
        public Dictionary<string, string> PobierzSektorySpolekGPW()
        {
            Dictionary<string, string> listaSektorow = new Dictionary<string, string>();

            string s = DownloadWebPage("http://www.gpw.pl/informacje_o_notowanych_akcjach");
            //string regexPattern = "=[0-9a-z]{3}>[a-zA-Z0-9]*</a>";
            string regexPattern = "<td class=\"left\">[0-9a-zA-Z]*</td>\n\t\t<td class=\"center\">[a-zA-Zół]*</td>\n\t\t<td class=\"center\">[A-Z]*</td>\n\t\t<td class=\"center\">[A-Z]*</td>\n\t\t<td>[a-zA-Zął ]*</td>\n\t\t<td class=\"center\">[a-z]*";
            Regex re = new Regex(regexPattern);

            Regex r = new Regex(regexPattern);
            string tmp;
            for (Match m = r.Match(s); m.Success; m = m.NextMatch())
            {
                tmp = m.ToString();
                listaSektorow.Add(tmp.Substring(17, 3).ToLower(), tmp.Substring(tmp.Length-3, 3));
            }

            return listaSektorow;
        }
        public void AktualizujSektorySpolek(Dictionary<string, string> daneSpolek)
        {
            foreach (var spolka in daneSpolek)
            {
                UpdateSektorSpolki(spolka.Key, spolka.Value);
            }
        }
        public List<string> PobierzNoweSpolki()
        {
            List<string> aktualneSpolki = PobierzListeSpolek();
            List<string> posiadaneSpolki = new List<string>();

            string selectCommand = "show tables;";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable tabele = new System.Data.DataTable();
            da.Fill(tabele);

            for (int row = 0; row < tabele.Rows.Count; row++)
            {
                posiadaneSpolki.Add(tabele.Rows[row].ItemArray[0].ToString());
            }
            foreach (string spolka in posiadaneSpolki)
            {
                aktualneSpolki.Remove(spolka);
            }
            return aktualneSpolki;
        }
        // SWIECZKI
        public void InsertDaneHistoryczneSpolki(string sym, List<Swieczka> daneHistoryczne)//Tworzenie zapytania dodającego dane historyczne spółki
        {
            string insertQuery = "";
            foreach (Swieczka dayInfo in daneHistoryczne)
            {
                insertQuery = "INSERT INTO `dts`.`" + sym + "` (`data`,`otwarcie`,`max`,`min`,`zamkniecie`,`wolumen`) VALUES ";
                insertQuery += "('" + dayInfo.GetData().Year.ToString() + "-" + dayInfo.GetData().Month.ToString() + "-" + dayInfo.GetData().Day.ToString() + "', " + dayInfo.GetOpen() + ", " + dayInfo.GetMax() + ", " + dayInfo.GetMin() + ", " + dayInfo.GetClose() + ", " + dayInfo.GetVolume() + ")";
                insertQuery += ";";
                ExecuteNonQuery(insertQuery);
            }
        }
        public List<Swieczka> SelectDaneHistoryczne(string sym, string sDate, string fDate/*DateTime sDate, DateTime fDate*/)
        {
            List<Swieczka> daneHistoryczne = new List<Swieczka>();
            // using DateTime: string selectCommand = "SELECT data, otwarcie, min, max, zamkniecie, wolumen FROM `dts`.`" + sym + "` WHERE data > '" + sDate.Date.ToString() + "' AND data < '" + fDate.Date.ToString() + "';";
            /*using string*/string selectCommand = "SELECT data, otwarcie, min, max, zamkniecie, wolumen FROM `dts`.`" + sym + "` WHERE data > '" + sDate + "' AND data < '" + fDate + "';";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable dane = new System.Data.DataTable();
            try
            {
                da.Fill(dane);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

            for (int row = 0; row < dane.Rows.Count; row++)
            {
                Swieczka s = new Swieczka(DateTime.Parse(dane.Rows[row].ItemArray[0].ToString()), Double.Parse(dane.Rows[row].ItemArray[1].ToString()), Double.Parse(dane.Rows[row].ItemArray[2].ToString()), Double.Parse(dane.Rows[row].ItemArray[3].ToString()), Double.Parse(dane.Rows[row].ItemArray[4].ToString()), int.Parse(dane.Rows[row].ItemArray[5].ToString()));
                daneHistoryczne.Add(s);
            }
            return daneHistoryczne;
        }
        public List<Swieczka> SelectDaneHistoryczne(string sym)
        {
            List<Swieczka> daneHistoryczne = new List<Swieczka>();
            string selectCommand = "SELECT data, otwarcie, min, max, zamkniecie, wolumen FROM `dts`.`" + sym + "`;";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable dane = new System.Data.DataTable();
            da.Fill(dane);

            for (int row = 0; row < dane.Rows.Count; row++)
            {
                Swieczka s = new Swieczka(DateTime.Parse(dane.Rows[row].ItemArray[0].ToString()), Double.Parse(dane.Rows[row].ItemArray[1].ToString()), Double.Parse(dane.Rows[row].ItemArray[2].ToString()), Double.Parse(dane.Rows[row].ItemArray[3].ToString()), Double.Parse(dane.Rows[row].ItemArray[4].ToString()), int.Parse(dane.Rows[row].ItemArray[5].ToString()));
                daneHistoryczne.Add(s);
            }
            return daneHistoryczne;
        }
        public List<Swieczka> PobierzDaneHistoryczneSpolki(string sym)
        {
            List<Swieczka> pobraneDane = new List<Swieczka>();
            //System.Threading.Thread.Sleep(2500);
            String s = "http://www.stooq.pl/q/d/l/?s=" + sym + "&i=d";
            CookieContainer cookieJar = new CookieContainer(); // kontener na ciasteczka
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create("http://www.stooq.pl"); // stworzenie requesta 
            request.CookieContainer = cookieJar; // przypisanie do requesta kontenera na ciasteczka
            request.GetResponse(); // wywolanie zapytania -> w tym momencie sie zapisze ciasteczko
            request = (HttpWebRequest)HttpWebRequest.Create(s); // stworzenie nowego zapytania
            request.CookieContainer = cookieJar; // przypisanie wypelnionego ciasteczka do nowego zapytania
            HttpWebResponse response = (HttpWebResponse)request.GetResponse(); // wywolanie zapytania
            Stream str = response.GetResponseStream(); // pobranie strumienia wejsciowego
            byte[] buffer = new byte[1000000]; // stworzenie buforu

            int bytesToRead = (int)buffer.Length;
            int bytesRead = 0;
            while (bytesToRead > 0)
            {
                int n = str.Read(buffer, bytesRead, bytesToRead);
                if (n == 0)
                    break;
                bytesRead += n;
                bytesToRead -= n;
            }
            String tmp = ASCIIEncoding.ASCII.GetString(buffer);
            tmp = tmp.Substring(57, bytesRead - 58);
            tmp = tmp.Replace(',', ';');
            string[] daneDzienne = tmp.Split('\n');

            foreach (string dzien in daneDzienne)
            {
                string[] dA = dzien.Split(';');
                try
                {
                    string[] data = dA[0].Split('-');
                    DateTime date = new DateTime(int.Parse(data[0]), int.Parse(data[1]), int.Parse(data[2]));
                    Swieczka sw = new Swieczka();
                    if (dA.Length == 6) sw = new Swieczka(date.Date, double.Parse(dA[1]), double.Parse(dA[2]), double.Parse(dA[3]), double.Parse(dA[4]), int.Parse(dA[5]));
                    else sw = new Swieczka(date.Date, double.Parse(dA[1]), double.Parse(dA[2]), double.Parse(dA[3]), double.Parse(dA[4]), 0);
                    pobraneDane.Add(sw);
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                }

            }

            return pobraneDane;
        }
        public List<Swieczka> PobierzDaneHistoryczneSpolki(string sym, string sDate, string fDate)
        {

            string[] dat = sDate.Split('-');
            sDate = dat[0] + dat[1] + dat[2];
            dat = fDate.Split('-');
            fDate = dat[0] + dat[1] + dat[2];

            List<Swieczka> pobraneDane = new List<Swieczka>();
            try
            {
                String s = "http://www.stooq.pl/q/d/l/?s=" + sym + "&d1=" + sDate + "&d2=" + fDate + "&i=d";
                CookieContainer cookieJar = new CookieContainer(); // kontener na ciasteczka
                HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create("http://www.stooq.pl"); // stworzenie requesta 
                request.CookieContainer = cookieJar; // przypisanie do requesta kontenera na ciasteczka
                request.GetResponse(); // wywolanie zapytania -> w tym momencie sie zapisze ciasteczko
                request = (HttpWebRequest)HttpWebRequest.Create(s); // stworzenie nowego zapytania
                request.CookieContainer = cookieJar; // przypisanie wypelnionego ciasteczka do nowego zapytania
                HttpWebResponse response = (HttpWebResponse)request.GetResponse(); // wywolanie zapytania
                Stream str = response.GetResponseStream(); // pobranie strumienia wejsciowego
                byte[] buffer = new byte[1000000]; // stworzenie buforu

                int bytesToRead = (int)buffer.Length;
                int bytesRead = 0;
                while (bytesToRead > 0)
                {
                    int n = str.Read(buffer, bytesRead, bytesToRead);
                    if (n == 0)
                        break;
                    bytesRead += n;
                    bytesToRead -= n;
                }
                    String tmp = ASCIIEncoding.ASCII.GetString(buffer);
                    tmp = tmp.Substring(57, bytesRead - 58);
                    tmp = tmp.Replace(',', ';');
                    string[] daneDzienne = tmp.Split('\n');

                foreach (string dzien in daneDzienne)
                {
                    string[] dA = dzien.Split(';');
                    string[] data = dA[0].Split('-');
                    DateTime date = new DateTime(int.Parse(data[0]), int.Parse(data[1]), int.Parse(data[2]));
                    Swieczka sw = new Swieczka();
                    if (dA.Length == 6) sw = new Swieczka(date.Date, double.Parse(dA[1]), double.Parse(dA[2]), double.Parse(dA[3]), double.Parse(dA[4]), int.Parse(dA[5]));
                    else sw = new Swieczka(date.Date, double.Parse(dA[1]), double.Parse(dA[2]), double.Parse(dA[3]), double.Parse(dA[4]), 0);
                    pobraneDane.Add(sw);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return pobraneDane;

        }
        // TABELE
        public void CreateTableSpolka(List<string> listaSpolek)//FILE GENERATED TO DO MANUALLY//Tworzenie zapytania tworzącego tablice spółek
        {
            StringBuilder createQuery = new StringBuilder();
            StreamWriter sw = new StreamWriter("triggers.sql", true);
            foreach (string s in listaSpolek)
            {
                createQuery.AppendLine("CREATE TABLE  IF NOT EXISTS `" + s + "` ( ");
                createQuery.AppendLine("  `data` date NOT NULL, ");
                createQuery.AppendLine("  `otwarcie` double NOT NULL, ");
                createQuery.AppendLine("  `min` double NOT NULL, ");
                createQuery.AppendLine("  `max` double NOT NULL, ");
                createQuery.AppendLine("  `zamkniecie` double NOT NULL, ");
                createQuery.AppendLine("  `wolumen` int(11) NOT NULL, ");
                createQuery.AppendLine("  PRIMARY KEY (`data`) ");
                createQuery.AppendLine(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci ;");

                sw.WriteLine("delimiter $$");
                sw.WriteLine("CREATE  ");
                sw.WriteLine("DEFINER=`root`@`localhost` ");
                sw.WriteLine("TRIGGER `dts`.`" + s + "OnInsert` ");
                sw.WriteLine("AFTER INSERT ON `dts`.`" + s + "` ");
                sw.WriteLine("FOR EACH ROW  ");
                sw.WriteLine("BEGIN ");
                sw.WriteLine(" ");
                sw.WriteLine("    UPDATE dts.Spolka SET kapitalizacja = liczbaAkcji * (SELECT zamkniecie  FROM  dts." + s + " ORDER BY data DESC LIMIT 1)  ");
                sw.WriteLine("    WHERE dts.spolka.symbol = '" + s + "'; ");
                sw.WriteLine(" ");
                sw.WriteLine("END ");
                sw.WriteLine("$$ ");
                sw.WriteLine(" ");

            }
            //System.Windows.Forms.MessageBox.Show(createQuery);
            sw.Close();
            ExecuteNonQuery(createQuery.ToString());
        }
        //public void CreateTableSpolka(List<string> listaSpolek)
        //{
        //    StringBuilder createQuery = new StringBuilder();
        //    foreach (string s in listaSpolek)
        //    {
        //        createQuery.AppendLine("-- -----------------------------------------------------\n");
        //        createQuery.AppendLine("-- Table `dts`.`" + s + "`\n");
        //        createQuery.AppendLine("-- -----------------------------------------------------\n");
        //        createQuery.AppendLine();
        //        createQuery.AppendLine("delimiter $$");
        //        createQuery.AppendLine();
        //        createQuery.AppendLine("CREATE TABLE  IF NOT EXISTS `" + s + "` ( ");
        //        createQuery.AppendLine("  `data` date NOT NULL, ");
        //        createQuery.AppendLine("  `otwarcie` double NOT NULL, ");
        //        createQuery.AppendLine("  `min` double NOT NULL, ");
        //        createQuery.AppendLine("  `max` double NOT NULL, ");
        //        createQuery.AppendLine("  `zamkniecie` double NOT NULL, ");
        //        createQuery.AppendLine("  `wolumen` int(11) NOT NULL, ");
        //        createQuery.AppendLine("  PRIMARY KEY (`data`) ");
        //        createQuery.AppendLine(") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci $$ ");
        //        createQuery.AppendLine(" ");
        //        createQuery.AppendLine("CREATE  ");
        //        createQuery.AppendLine("DEFINER=`root`@`localhost` ");
        //        createQuery.AppendLine("TRIGGER `dts`.`" + s + "OnInsert` ");
        //        createQuery.AppendLine("AFTER INSERT ON `dts`.`" + s + "` ");
        //        createQuery.AppendLine("FOR EACH ROW  ");
        //        createQuery.AppendLine("BEGIN ");
        //        createQuery.AppendLine(" ");
        //        createQuery.AppendLine("    UPDATE dts.Spolka SET kapitalizacja = liczbaAkcji * (SELECT zamkniecie  FROM  dts." + s + " ORDER BY data DESC LIMIT 1)  ");
        //        createQuery.AppendLine("    WHERE dts.spolka.symbol = '" + s + "'; ");
        //        createQuery.AppendLine(" ");
        //        createQuery.AppendLine("END ");
        //        createQuery.AppendLine("$$ ");
        //        createQuery.AppendLine(" ");

        //    }
        //    //System.Windows.Forms.MessageBox.Show(createQuery);
        //    StreamWriter sw = new StreamWriter("createQuery.txt");
        //    sw.Write(createQuery.ToString());
        //    sw.Close();
        //    //ExecuteNonQuery(createQuery.ToString());
        //}
        // GOTOWKA
        public double UpdateGotowka(double iloscGotowki)
        {
            double gotowka = SelectGotowka();
            string updateCommand = "UPDATE `dts`.`gotowka` SET `dostepnaGotowka`='" + /*(gotowka + */iloscGotowki/*)*/.ToString() + "' WHERE `dostepnaGotowka`='" + gotowka.ToString() + "';";
            ExecuteNonQuery(updateCommand);
            return SelectGotowka();
        }
        public double SelectGotowka()
        {
            double gotowka;
            try
            {
                string selectCommand = "SELECT dostepnaGotowka FROM `dts`.`gotowka`;";
                MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
                System.Data.DataTable got = new System.Data.DataTable();
                da.Fill(got);
                gotowka = double.Parse(got.Rows[0].ItemArray[0].ToString());
                return gotowka;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return -1;
        }
        // WALOR
        public Walor SelectWalor(string sym)
        {
            Walor w = new Walor();
            string selectCommand = "SELECT symbol, ilosc, isShort, srKupno, owner FROM `dts`.`portfel` WHERE symbol = '" + sym + "';";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable walory = new System.Data.DataTable();
            try
            {
                da.Fill(walory);

                for (int row = 0; row < walory.Rows.Count; row++)
                {
                    w.setSymbol(walory.Rows[row].ItemArray[0].ToString());
                    w.setLiczba(int.Parse(walory.Rows[row].ItemArray[1].ToString()));
                    w.setIsShort(bool.Parse(walory.Rows[row].ItemArray[2].ToString()));
                    w.setAvgBuy(Double.Parse(walory.Rows[row].ItemArray[3].ToString()));
                    w.setOwner(walory.Rows[row].ItemArray[4].ToString());
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return w;
        }
        public List<Walor> SelectWalory()
        {
            List<Walor> listaWalorow = new List<Walor>();
            Walor w = new Walor();
            string selectCommand = "SELECT symbol, ilosc, isShort, srKupno, owner FROM `dts`.`portfel`;";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable walory = new System.Data.DataTable();
            try
            {
                da.Fill(walory);

                for (int row = 0; row < walory.Rows.Count; row++)
                {
                    w = new Walor();
                    w.setSymbol(walory.Rows[row].ItemArray[0].ToString());
                    w.setLiczba(int.Parse(walory.Rows[row].ItemArray[1].ToString()));
                    w.setIsShort(bool.Parse(walory.Rows[row].ItemArray[2].ToString()));
                    w.setAvgBuy(Double.Parse(walory.Rows[row].ItemArray[3].ToString()));
                    w.setOwner(walory.Rows[row].ItemArray[4].ToString());
                    listaWalorow.Add(w);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

            return listaWalorow;
        }
        public void UpdateWalor(Walor w)
        {
            string commandText = "UPDATE `dts`.`portfel` SET ilosc = " + w.getLiczba().ToString() + ", isShort = " + w.getIsShort().ToString() + ", srKupno = " + w.getAvgBuy().ToString() + " WHERE symbol = '" + w.getSymbol() + "';";

            ExecuteNonQuery(commandText);
        }
        public void DeleteWalor(string sym)
        {
            string commandText = "DELETE FROM `dts`.`portfel` WHERE symbol = '" + sym + "';";

            ExecuteNonQuery(commandText); 
        }
        public bool SelectWalorExists(string sym)
        {
            Walor w = new Walor();
            string selectCommand = "SELECT symbol, ilosc, isShort, srKupno FROM `dts`.`portfel` WHERE symbol = '" + sym + "';";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable walory = new System.Data.DataTable();
            try
            {
                da.Fill(walory);
                if (walory.Rows.Count == 0) return false;
                else return true;
            }
            catch
            {
                return false;
            }
        }
        public void InsertWalor(Walor w)
        {
            string commandText = "INSERT INTO `dts`.`portfel` (`symbol`, `ilosc`, `isShort`, `srKupno`,`owner`) VALUES('";
            commandText += w.getSymbol() + "', ";
            commandText += w.getLiczba().ToString() + ", ";
            commandText += w.getIsShort().ToString() + ", ";
            commandText += w.getAvgBuy().ToString() + ", '";
            commandText += w.getOwner() + "');";

            ExecuteNonQuery(commandText);
        }
        // TRANSAKCJA
        public List<Transakcja> SelectTransakcje()
        {
            List<Transakcja> listaTransakcji = new List<Transakcja>();
            string selectCommand = "SELECT spolka, rodzajTransakcji, ilosc, cena, prowizja, zysk, data, owner FROM `dts`.`historiaportfela`;";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable transakcje = new System.Data.DataTable();
            try
            {
                da.Fill(transakcje);

                for (int row = 0; row < transakcje.Rows.Count; row++)
                {
                    Transakcja t = new Transakcja(transakcje.Rows[row].ItemArray[0].ToString(), int.Parse(transakcje.Rows[row].ItemArray[2].ToString()), int.Parse(transakcje.Rows[row].ItemArray[1].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[3].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[4].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[5].ToString()), StringToDateTime(transakcje.Rows[row].ItemArray[6].ToString()), transakcje.Rows[row].ItemArray[7].ToString());
                    listaTransakcji.Add(t);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return listaTransakcji;
        }
        public List<Transakcja> SelectTransakcjeSpolki(string sym)
        {
            List<Transakcja> listaTransakcji = new List<Transakcja>();
            string selectCommand = "SELECT spolka, rodzajTransakcji, ilosc, cena, prowizja, zysk, data FROM `dts`.`historiaportfela` WHERE spolka = '" + sym + "';";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable transakcje = new System.Data.DataTable();
            try
            {
                da.Fill(transakcje);

                for (int row = 0; row < transakcje.Rows.Count; row++)
                {
                    Transakcja t = new Transakcja(transakcje.Rows[row].ItemArray[0].ToString(), int.Parse(transakcje.Rows[row].ItemArray[1].ToString()), int.Parse(transakcje.Rows[row].ItemArray[2].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[3].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[4].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[5].ToString()), StringToDateTime(transakcje.Rows[row].ItemArray[6].ToString()), transakcje.Rows[row].ItemArray[7].ToString());
                    listaTransakcji.Add(t);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return listaTransakcji;
        }
        public List<Transakcja> SelectTransakcjeSpolki(string sym, DateTime sDate, DateTime fDate)
        {
            List<Transakcja> listaTransakcji = new List<Transakcja>();
            string selectCommand = "SELECT spolka, rodzajTransakcji, ilosc, cena, prowizja, zysk, data FROM `dts`.`historiaportfela` WHERE spolka = '" + sym + "' AND data > '" + DateTimeToString(sDate) + "' AND data < '" + DateTimeToString(fDate) + "';";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable transakcje = new System.Data.DataTable();
            try
            {
                da.Fill(transakcje);

                for (int row = 0; row < transakcje.Rows.Count; row++)
                {
                    Transakcja t = new Transakcja(transakcje.Rows[row].ItemArray[0].ToString(), int.Parse(transakcje.Rows[row].ItemArray[1].ToString()), int.Parse(transakcje.Rows[row].ItemArray[2].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[3].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[4].ToString()), Double.Parse(transakcje.Rows[row].ItemArray[5].ToString()), StringToDateTime(transakcje.Rows[row].ItemArray[6].ToString()), transakcje.Rows[row].ItemArray[7].ToString());
                    listaTransakcji.Add(t);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return listaTransakcji;
        }
        public void InsertTransakcja(Transakcja t)
        {
            string commandText = "INSERT INTO `dts`.`historiaportfela` (`spolka`, `rodzajTransakcji`, `ilosc`, `cena`, `prowizja`, `zysk`, `data`, `owner`) VALUES('";
            commandText += t.getSymbol() + "', ";
            commandText += t.getRodzaj().ToString() + ", ";
            commandText += t.getLiczba().ToString() + ", ";
            commandText += t.getCena().ToString() + ", ";
            commandText += t.getProwizja().ToString() + ", ";
            commandText += t.getZysk().ToString() + ", '";
            string[] date = t.getData().ToString().Split('/');
            string[] godzina = date[2].Split(':');
            date[2] = date[2].Substring(0, 4);
            godzina[0] = godzina[0].Remove(0, 5);
            string[] g = godzina[2].Split(' ');
            godzina[2] = g[0];
            if (g[1].Equals("PM")) godzina[0] = (int.Parse(godzina[0]) + 12).ToString();
            if (date[0].Length == 1) date[0] = "0" + date[0];
            string data = date[2]+"-"+date[0]+"-"+date[1]+" "+godzina[0]+":"+godzina[1]+":"+godzina[2];
            commandText += data + "', '";
            commandText += t.getOwner() + "');";
            ExecuteNonQuery(commandText);
        }
        public void InsertTransakcja(List<Transakcja> listaTransakcji, Walor w)
        {
            foreach (Transakcja t in listaTransakcji)
                InsertTransakcja(t);
            if (SelectWalorExists(w.getSymbol()) && w.getLiczba() != 0) UpdateWalor(w);
            else if (w.getLiczba() == 0) DeleteWalor(w.getSymbol());
            else InsertWalor(w);

        }
        // TRANSAKCJA
        public List<string> SelectObserwowaneSpolki()
        {
            List<string> obserwowaneSpolki = new List<string>();

            string spolka = "";
            string selectCommand = "SELECT idobserwowanespolki FROM `dts`.`obserwowanespolki`;";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable obserwowane = new System.Data.DataTable();
            try
            {
                da.Fill(obserwowane);

                for (int row = 0; row < obserwowane.Rows.Count; row++)
                {
                    spolka = (obserwowane.Rows[row].ItemArray[0].ToString());
                    obserwowaneSpolki.Add(spolka);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }

            return obserwowaneSpolki;
        }
        public void InsertObserwowanaSpolka(string sym)
        {
            string commandText = "INSERT INTO `dts`.`obserwowanespolki` (`idobserwowanespolki`) VALUES('";
            commandText += sym + "');";

            ExecuteNonQuery(commandText);
        }
        public void DeleteObserwowanaSpolka(string sym)
        {
            string commandText = "DELETE FROM `dts`.`obserwowanespolki` WHERE `obserwowanespolki`.`idobserwowanespolki` = '";
            commandText += sym + "';";

            ExecuteNonQuery(commandText);
        }

        // TEMP
        public string WygenerujDaneZPliku(string sym)
        {
            StringBuilder insertCommand = new StringBuilder();
            StreamReader sr = new StreamReader("C:\\Users\\Sturm\\Downloads\\DTS\\"+sym+"_d.csv");

            sr.ReadLine();
            string tresc = sr.ReadToEnd();
            sr.Close();
            
            int i = 0;
            string[] data = tresc.Split('\n');
            insertCommand.Append("INSERT INTO `dts`.`" + sym + "`(`data`,`otwarcie`,`max`,`min`,`zamkniecie`,`wolumen`) VALUES");
            foreach (string d in data)
            {
                string[] items = d.Split(',');
                if (items[0] != "\n" && items.Length == 6)
                {
                    insertCommand.Append("('" + items[0] + "', " + items[1] + ", " + items[2] + ", " + items[3] + ", " + items[4] + ", " + items[5] + ")");
                    if (i != data.Length - 2) insertCommand.Append(",");
                }
                else if (items.Length == 5)
                {
                    insertCommand.Append("('" + items[0] + "', " + items[1] + ", " + items[2] + ", " + items[3] + ", " + items[4] + ", 0)");
                    if (i != data.Length - 2) insertCommand.Append(",");
                }
                i++;
            }
            insertCommand.Append(";");
            return insertCommand.ToString();
        }
        public void AktualizujDaneSpolki(string sym)
        {
            DateTime dt = PobierzDateOstatniejAktualizacji(sym);
            dt = dt.AddDays(1);
            string[] sData = DateTimeToString(dt).Split(' ');
            string[] fData = DateTimeToString(DateTime.Now).Split(' ');
            List<Swieczka> aktualizacjaDanychHistorycznych = PobierzDaneHistoryczneSpolki(sym, sData[0], fData[0]);
            InsertDaneHistoryczneSpolki(sym, aktualizacjaDanychHistorycznych);
        }
        /*public void AktualizujWybraneSpolki()
        {
            List<Swieczka> daneHistoryczne = new List<Swieczka>();
            List<string> listaSpolek = new List<string>();
            List<string> doUsuniecia = new List<string>();
            StreamReader sr = new StreamReader("listaSpolek.dat");
            
            string[] spolkiZPliku = sr.ReadToEnd().Split(';');
            sr.Close();

            StreamWriter sw = new StreamWriter("listaSpolek.dat");
            
            foreach(string spolka in spolkiZPliku)
                listaSpolek.Add(spolka);

            int i = 0;
            foreach (string spolka in listaSpolek)
            {
                i++;
                if (i <= 5)
                {
                    daneHistoryczne = PobierzDaneHistoryczneSpolki(spolka);
                    InsertDaneHistoryczneSpolki(spolka, daneHistoryczne);
                    doUsuniecia.Add(spolka);
                    //System.Windows.Forms.MessageBox.Show(spolka);
                }
                else
                {
                    int j = 0;
                    foreach (string sp in doUsuniecia)
                        listaSpolek.Remove(sp);

                    foreach(string sp in listaSpolek)
                    {
                        j++;
                        if (j < listaSpolek.Count) sw.Write(sp + ";");
                        else sw.Write(sp);
                    }
                    break;
                }

            }
            sw.Close();
        }*/
        
        // UŻYTEK WSPOLNY
        public string DownloadWebPage(string link)
        {
            string result = string.Empty;

            WebRequest wrq = WebRequest.Create(link);
            WebResponse wrs = wrq.GetResponse();

            StreamReader strdr = new StreamReader(wrs.GetResponseStream());
            result = strdr.ReadToEnd();

            strdr.Close();
            wrs.Close();

            return result;
        }
        public void ExecuteNonQuery(string commandText)//Połączenie do bazy i wykonanie zapytania SQL
        {
            try
            {
                MySqlConnection con = new MySqlConnection(connectionString);
                con.Open();
                MySqlCommand command = new MySqlCommand("query", con);
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = commandText;
                //System.Windows.Forms.MessageBox.Show(commandText);
            
                command.ExecuteNonQuery();
                
                command.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message, "Error");
            }

        }
        public Dictionary<string, Notowania> PobierzAktualneNotowania()
        {
            Dictionary<string, Notowania> aktualneNotowania = new Dictionary<string, Notowania>();

            string html = DownloadWebPage("http://stooq.pl/notowania/?kat=g2");
            string symbol = "";
            string tmp = "";
            Notowania s = new Notowania();
            string regexPattern = "[a-zA-Z0-9]*>[a-zA-Z0-9]*</a></td><td><span id=aq_[a-zA-Z0-9]*_o>[0-9]*.[0-9]*</span></td><td><span id=aq_[a-zA-Z0-9]*_h>[a-z0-9]*.[a-z0-9]*</span></td><td><span id=aq_[a-zA-Z0-9]*_l>[a-z0-9]*.[a-z0-9]*</span></td><td><b><span id=aq_[a-zA-Z0-9]*_c[0-9]>[a-z0-9]*.[a-z0-9]*</span></b></td><td id=c[0-9]><b><span id=aq_[a-zA-Z0-9]*_m[0-9]>[+-a-z0-9%]*</span></b></td><td><span id=aq_[a-zA-Z0-9]*_v[0-9]>[a-z0-9]*.[a-z0-9.]*</span></td><td><span id=aq_[a-zA-Z0-9]*_r[0-9]>[a-z0-9]*.[a-z0-9.]*</span></td><td><span id=aq_[a-zA-Z0-9]*_dt[0-9]>[a-zA-Z0-9: ,]*";
            Regex r = new Regex(regexPattern);

            for (Match m = r.Match(html); m.Success; m = m.NextMatch())
            {
                //bool c = true;
                string mnoznik = "";
                double d = 0;
                tmp = m.ToString();
                symbol = tmp.Substring(0, 3);
                string[] separator = { "</td><td>", "</span></b>" };
                string[] parts = tmp.Split(separator, StringSplitOptions.None);
                try
                {
                    s = new Notowania();
                    string o = parts[1].Substring(18, parts[1].Length - 25);
                    s.SetOpen(double.Parse(o));
                    o = parts[2].Substring(18, parts[2].Length - 25);
                    s.SetMax(double.Parse(o));
                    o = parts[3].Substring(18, parts[3].Length - 25);
                    s.SetMin(double.Parse(o));
                    o = parts[4].Substring(22, parts[4].Length - 22);
                    s.SetClose(double.Parse(o));
                    o = parts[5].Substring(parts[5].LastIndexOf('>') + 1, parts[5].Length - (parts[5].LastIndexOf('>') + 2));
                    s.SetZmiana(double.Parse(o));
                    if (parts[7].Contains("."))
                    {
                        string[] volumeParts = parts[7].Split('.');
                        volumeParts[0] = volumeParts[0].Remove(0, 19);
                        mnoznik = volumeParts[1].Substring(volumeParts[1].Length - 8, 1);
                        volumeParts[1] = volumeParts[1].Remove(volumeParts[1].Length - 8);
                        o = volumeParts[0] + "." + volumeParts[1];
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else if (parts[7].Substring(parts[7].Length - 8, 1) == "k" || parts[7].Substring(parts[7].Length - 8, 1) == "m" || parts[7].Substring(parts[7].Length - 8, 1) == "g")
                    {
                        o = parts[7].Substring(19, parts[7].Length - 27);
                        mnoznik = parts[7].Substring(parts[7].Length - 8, 1);
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else
                    {
                        o = parts[7].Substring(19, parts[7].Length - 26);
                        s.SetVolume(int.Parse(o));
                    }
                    /*if (s.GetVolume() == 0)
                        c = false;*/
                    s.SetData(DateTime.Now);
                    aktualneNotowania.Add(symbol, s);
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                }
            }

            return aktualneNotowania;
        }
        public Dictionary<string, Notowania> PobierzAktualneNotowaniaWeekend()
        {
            Dictionary<string, Notowania> aktualneNotowania = new Dictionary<string, Notowania>();

            string html = DownloadWebPage("http://stooq.pl/notowania/?kat=g2");
            string symbol = "";
            string tmp = "";
            Notowania s = new Notowania();
            string regexPattern = "[a-zA-Z0-9&]*=[0-9]*>[a-zA-Z0-9]*</a></td><td>[0-9]*.[0-9]*</td><td>[0-9]*.[0-9]*</td><td>[0-9]*.[0-9]*</td><td><b>[0-9]*.[0-9]*</b></td><td id=c[0-9]><b>[-+.0-9%]*</b></td><td>[0-9]*.[0-9kgm]*</td><td>[0-9]*.[0-9kgm]*</td><td>[0-9a-zA-Z,: ]*";
            Regex r = new Regex(regexPattern);

            for (Match m = r.Match(html); m.Success; m = m.NextMatch())
            {
                //bool c = true;
                string mnoznik = "";
                double d = 0;
                tmp = m.ToString();
                symbol = tmp.Substring(0, 3);
                string[] separator = { "</td><td>", "</b>" };
                string[] parts = tmp.Split(separator, StringSplitOptions.None);
                try
                {
                    s = new Notowania();
                    string o = "";
                    s.SetOpen(double.Parse(parts[1]));
                    s.SetMax(double.Parse(parts[2]));
                    s.SetMin(double.Parse(parts[3]));
                    o = parts[4].Substring(3, parts[4].Length - 3);
                    s.SetClose(double.Parse(o));
                    //TODO po 17:45 wyłuskanie
                    o = parts[5].Substring(parts[5].LastIndexOf('>') + 1, parts[5].Length - (parts[5].LastIndexOf('>') + 2));
                    s.SetZmiana(double.Parse(o));
                    if (parts[7].Contains("."))
                    {
                        string[] volumeParts = parts[7].Split('.');
                        mnoznik = volumeParts[1].Substring(volumeParts[1].Length - 1, 1);
                        volumeParts[1] = volumeParts[1].Remove(volumeParts[1].Length - 1);
                        o = volumeParts[0] + "." + volumeParts[1];
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else if (parts[7].Substring(parts[7].Length - 1, 1) == "k" || parts[7].Substring(parts[7].Length - 1, 1) == "m" || parts[7].Substring(parts[7].Length - 1, 1) == "g")
                    {
                        o = parts[7].Substring(0, parts[7].Length - 1);
                        mnoznik = parts[7].Substring(parts[7].Length - 1, 1);
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else
                    {
                        //o = parts[7].Substring(19, parts[7].Length - 26);
                        s.SetVolume(int.Parse(parts[7]));
                    }
                    //if (s.GetVolume() == 0)
                    //    c = false;
                    s.SetData(DateTime.Now);
                    aktualneNotowania.Add(symbol, s);
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                }
            }

            return aktualneNotowania;
        }
        public Dictionary<string, Swieczka> PobierzAktualneCeny()
        {
            Dictionary<string, Swieczka> aktualneSwieczki = new Dictionary<string, Swieczka>();

            string html = DownloadWebPage("http://stooq.pl/notowania/?kat=g2");
            string symbol = "";
            string tmp = "";
            Swieczka s = new Swieczka();
            string regexPattern = "[a-zA-Z0-9]*>[a-zA-Z0-9]*</a></td><td><span id=aq_[a-zA-Z0-9]*_o>[0-9]*.[0-9]*</span></td><td><span id=aq_[a-zA-Z0-9]*_h>[a-z0-9]*.[a-z0-9]*</span></td><td><span id=aq_[a-zA-Z0-9]*_l>[a-z0-9]*.[a-z0-9]*</span></td><td><b><span id=aq_[a-zA-Z0-9]*_c[0-9]>[a-z0-9]*.[a-z0-9]*</span></b></td><td id=c[0-9]><b><span id=aq_[a-zA-Z0-9]*_m[0-9]>[+-a-z0-9%]*</span></b></td><td><span id=aq_[a-zA-Z0-9]*_v[0-9]>[a-z0-9]*.[a-z0-9.]*</span></td><td><span id=aq_[a-zA-Z0-9]*_r[0-9]>[a-z0-9]*.[a-z0-9.]*</span></td><td><span id=aq_[a-zA-Z0-9]*_dt[0-9]>[a-zA-Z0-9: ,]*";
            Regex r = new Regex(regexPattern);
            
            for (Match m = r.Match(html); m.Success; m = m.NextMatch())
            {
                //bool c = true;
                string mnoznik = "";
                double d = 0;
                tmp = m.ToString();
                symbol = tmp.Substring(0, 3);
                string[] separator = {"</td><td>", "</span></b>"};
                string[] parts = tmp.Split(separator, StringSplitOptions.None);
                try
                {
                    s = new Swieczka();
                    string o = parts[1].Substring(18, parts[1].Length - 25);
                    s.SetOpen(double.Parse(o));
                    o = parts[2].Substring(18, parts[2].Length - 25);
                    s.SetMax(double.Parse(o));
                    o = parts[3].Substring(18, parts[3].Length - 25);
                    s.SetMin(double.Parse(o));
                    o = parts[4].Substring(22, parts[4].Length - 22);
                    s.SetClose(double.Parse(o));
                    if (parts[7].Contains("."))
                    {
                        string[] volumeParts = parts[7].Split('.');
                        volumeParts[0] = volumeParts[0].Remove(0, 19);
                        mnoznik = volumeParts[1].Substring(volumeParts[1].Length - 8, 1);
                        volumeParts[1] = volumeParts[1].Remove(volumeParts[1].Length - 8);
                        o = volumeParts[0] + "." + volumeParts[1];
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else if (parts[7].Substring(parts[7].Length - 8, 1) == "k" || parts[7].Substring(parts[7].Length - 8, 1) == "m" || parts[7].Substring(parts[7].Length - 8, 1) == "g")
                    {
                        o = parts[7].Substring(19, parts[7].Length - 27);
                        mnoznik = parts[7].Substring(parts[7].Length - 8, 1);
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else
                    {
                        o = parts[7].Substring(19, parts[7].Length - 26);
                        s.SetVolume(int.Parse(o));
                    }
                    /*if (s.GetVolume() == 0)
                        c = false;*/
                    s.SetData(DateTime.Now);
                    aktualneSwieczki.Add(symbol, s);
                }
                catch(Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                }
            }

            return aktualneSwieczki;
        }
        public Dictionary<string, Swieczka> PobierzAktualneCenyWeekend()
        {
            Dictionary<string, Swieczka> aktualneSwieczki = new Dictionary<string, Swieczka>();

            string html = DownloadWebPage("http://stooq.pl/notowania/?kat=g2");
            string symbol = "";
            string tmp = "";
            Swieczka s = new Swieczka();
            string regexPattern = "[a-zA-Z0-9&]*=[0-9]*>[a-zA-Z0-9]*</a></td><td>[0-9]*.[0-9]*</td><td>[0-9]*.[0-9]*</td><td>[0-9]*.[0-9]*</td><td><b>[0-9]*.[0-9]*</b></td><td id=c[0-9]><b>[-+.0-9%]*</b></td><td>[0-9]*.[0-9kgm]*</td><td>[0-9]*.[0-9kgm]*</td><td>[0-9a-zA-Z,: ]*";
            Regex r = new Regex(regexPattern);
            
            for (Match m = r.Match(html); m.Success; m = m.NextMatch())
            {
                //bool c = true;
                string mnoznik = "";
                double d = 0;
                tmp = m.ToString();
                symbol = tmp.Substring(0, 3);
                string[] separator = {"</td><td>", "</b>"};
                string[] parts = tmp.Split(separator, StringSplitOptions.None);
                try
                {
                    s = new Swieczka();
                    string o = "";
                    s.SetOpen(double.Parse(parts[1]));
                    s.SetMax(double.Parse(parts[2]));
                    s.SetMin(double.Parse(parts[3]));
                    o = parts[4].Substring(3, parts[4].Length - 3);
                    s.SetClose(double.Parse(o));
                    if (parts[7].Contains("."))
                    {
                        string[] volumeParts = parts[7].Split('.');
                        mnoznik = volumeParts[1].Substring(volumeParts[1].Length -1, 1);
                        volumeParts[1] = volumeParts[1].Remove(volumeParts[1].Length - 1);
                        o = volumeParts[0] + "." + volumeParts[1];
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else if (parts[7].Substring(parts[7].Length - 1, 1) == "k" || parts[7].Substring(parts[7].Length - 1, 1) == "m" || parts[7].Substring(parts[7].Length - 1, 1) == "g")
                    {
                        o = parts[7].Substring(0, parts[7].Length - 1);
                        mnoznik = parts[7].Substring(parts[7].Length - 1, 1);
                        d = double.Parse(o);
                        switch (mnoznik)
                        {
                            case "k":
                                d *= 1000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "m":
                                d *= 1000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            case "g":
                                d *= 1000000000;
                                s.SetVolume(int.Parse(d.ToString()));
                                break;
                            default: break;
                        }
                    }
                    else
                    {
                        //o = parts[7].Substring(19, parts[7].Length - 26);
                        s.SetVolume(int.Parse(parts[7]));
                    }
                    /*if (s.GetVolume() == 0)
                        c = false;*/
                    s.SetData(DateTime.Now);
                    aktualneSwieczki.Add(symbol, s);
                }
                catch(Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                }
            }

            return aktualneSwieczki;
        }
        public DateTime PobierzDateOstatniejAktualizacji(string sym)
        {
            string selectCommand = "SELECT data FROM `dts`.`" + sym + "` ORDER BY data DESC LIMIT 1;";
            MySqlDataAdapter da = new MySqlDataAdapter(selectCommand, connectionString);
            System.Data.DataTable dane = new System.Data.DataTable();
            da.Fill(dane);

            return StringToDateTime(dane.Rows[0].ItemArray[0].ToString());
        }
        public string DateTimeToString(DateTime data)
        {
            string[] date = data.ToString().Split('/');
            string[] godzina = date[2].Split(':');
            date[2] = date[2].Substring(0, 4);
            godzina[0] = godzina[0].Remove(0, 5);
            string[] g = godzina[2].Split(' ');
            godzina[2] = g[0];
            if (g[1].Equals("PM")) godzina[0] = (int.Parse(godzina[0]) + 12).ToString();
            for (int i = 0; i < 2; i++)
            {
                int s = int.Parse(date[i]);
                if (s < 10) date[i] = "0" + s.ToString();
            }
            return date[2]+"-"+date[0]+"-"+date[1]+" "+godzina[0]+":"+godzina[1]+":"+godzina[2];
        }
        public DateTime StringToDateTime(string data)
        {
            string[] date = data.ToString().Split('/');
            string[] godzina = date[2].Split(':');
            date[2] = date[2].Substring(0, 4);
            godzina[0] = godzina[0].Remove(0, 5);
            string[] g = godzina[2].Split(' ');
            godzina[2] = g[0];
            if (g[1].Equals("PM"))
            {
                godzina[0] = (int.Parse(godzina[0]) + 12).ToString();
                if (int.Parse(godzina[0]) == 24) godzina[0] = "00";
            }
            DateTime d = new DateTime(int.Parse(date[2]), int.Parse(date[0]), int.Parse(date[1]), int.Parse(godzina[0]), int.Parse(godzina[1]), int.Parse(godzina[2]));
            return d;
        }
        public void ClearUserData()
        {
            string commandText = "TRUNCATE TABLE `dts`.`portfel`";
            ExecuteNonQuery(commandText);
            commandText = "TRUNCATE TABLE `dts`.`historiaportfela`";
            ExecuteNonQuery(commandText);
            UpdateGotowka(0);
        }
    }
}